using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using SoTaiChinh.Library.Common.SqlServer;
using Entity = SoTaiChinh.Library.Entity;
namespace SoTaiChinh.Library.Data
{
	public class ThanhVien
    {
		public ThanhVien() {}
		Connection db = new Connection();

        /// <summary>
        /// Inserts a record into the ThanhVien table.
        /// <summary>
        /// <param name="TenDangNhap"></param>
        /// <param name="QuyenTruyCap"></param>
        /// <param name="MatKhau"></param>
        /// <param name="TenDayDu"></param>
        /// <param name="NgaySinh"></param>
        /// <param name="DiaChi"></param>
        /// <param name="SoDienThoai"></param>
        /// <param name="NgayThamGia"></param>
        /// <param name="Email"></param>
        /// <param name="TrangThai"></param>
        /// <returns></returns>
	    public bool Insert(Entity.ThanhVien entity)
	    {
		    bool boolInsert = false;
		    SqlConnection SqlCnn = db.ReadFromWebConfig();
		    SqlDataAdapter SqlAdp = new SqlDataAdapter();
		    SqlCommand SqlCmd = new SqlCommand();
		    SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_ThanhVien_Insert";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
			    SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar,50).Value=entity.TenDangNhap;
			    SqlCmd.Parameters.Add("@QuyenTruyCap", SqlDbType.Int).Value=entity.QuyenTruyCap;
			    SqlCmd.Parameters.Add("@MatKhau", SqlDbType.NVarChar,50).Value=entity.MatKhau;
			    SqlCmd.Parameters.Add("@TenDayDu", SqlDbType.NVarChar,50).Value=entity.TenDayDu;
			    SqlCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value=entity.NgaySinh;
			    SqlCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar,4000).Value=entity.DiaChi;
			    SqlCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar,20).Value=entity.SoDienThoai;
			    SqlCmd.Parameters.Add("@NgayThamGia", SqlDbType.DateTime).Value=entity.NgayThamGia;
			    SqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar,50).Value=entity.Email;
			    SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value=entity.TrangThai;

			    SqlAdp.InsertCommand = SqlCmd;
			    SqlAdp.InsertCommand.ExecuteNonQuery();
			    SqlCmd.Transaction.Commit();
			    boolInsert = true;
			    SqlCnn.Close();
		    }
		    catch
		    {
			    boolInsert = false;
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return boolInsert;
	    }

        /// <summary>
        /// Selects a single record from the ThanhVien table.
        /// <summary>
        /// <returns>DataTable</returns>
	    public DataTable Select()
		{
		    DataTable dt = new DataTable();
		    SqlConnection SqlCnn = db.ReadFromWebConfig();
		    SqlDataAdapter SqlAdp = new SqlDataAdapter();
		    SqlCommand SqlCmd = new SqlCommand();
		    SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_ThanhVien_Select";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
			    SqlAdp.SelectCommand = SqlCmd;
			    SqlAdp.SelectCommand.ExecuteNonQuery();
			    SqlAdp.Fill(dt);
			    SqlCmd.Transaction.Commit();
			    SqlCnn.Close();
		    }
		    catch
		    {
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return dt;
	    }

        /// <summary>
        /// Selects a single record from the ThanhVien table.
        /// <summary>
        /// <returns>DataTable</returns>
	    public DataTable SelectPrimaryKey(Entity.ThanhVien entity)
	    {
		    DataTable dt = new DataTable();
			SqlConnection SqlCnn = db.ReadFromWebConfig();
			SqlDataAdapter SqlAdp = new SqlDataAdapter();
			SqlCommand SqlCmd = new SqlCommand();
			SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_ThanhVien_SelectPrimaryKey";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
			    SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar,50).Value=entity.TenDangNhap;

			    SqlAdp.SelectCommand = SqlCmd;
			    SqlAdp.SelectCommand.ExecuteNonQuery();
			    SqlAdp.Fill(dt);
			    SqlCmd.Transaction.Commit();
			    SqlCnn.Close();
		    }
		    catch
		    {
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return dt;
	    }

        /// <summary>
        /// Select search by QuyenTruyCap a single record from the ThanhVien table.
        /// <summary>
        /// <returns>DataTable</returns>
        public DataTable SearchByQuyen(string TenDangNhap, int QuyenTruyCap)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_ThanhVien_SearchByQuyenTruyCap";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = TenDangNhap;
                SqlCmd.Parameters.Add("@QuyenTruyCap", SqlDbType.Int).Value = QuyenTruyCap;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        /// <summary>
        /// Select search all QuyenTruyCap a single record from the ThanhVien table.
        /// <summary>
        /// <returns>DataTable</returns>
        public DataTable SearchAll(string TenDangNhap)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_ThanhVien_SearchAll";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = TenDangNhap;
                
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }

        /// <summary>
        /// Updates a record in the ThanhVien table.
        /// <summary>
        /// <param name="TenDangNhap"></param>
        /// <param name="QuyenTruyCap"></param>
        /// <param name="MatKhau"></param>
        /// <param name="TenDayDu"></param>
        /// <param name="NgaySinh"></param>
        /// <param name="DiaChi"></param>
        /// <param name="SoDienThoai"></param>
        /// <param name="NgayThamGia"></param>
        /// <param name="Email"></param>
        /// <param name="TrangThai"></param>
	    public bool Update(Entity.ThanhVien entity)
	    {
		    bool boolUpdate = false ;
		    SqlConnection SqlCnn = db.ReadFromWebConfig();
		    SqlDataAdapter SqlAdp = new SqlDataAdapter();
		    SqlCommand SqlCmd = new SqlCommand();
		    SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_ThanhVien_Update";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
			    SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar,50).Value=entity.TenDangNhap;
			    SqlCmd.Parameters.Add("@QuyenTruyCap", SqlDbType.Int).Value=entity.QuyenTruyCap;
			    SqlCmd.Parameters.Add("@MatKhau", SqlDbType.NVarChar,50).Value=entity.MatKhau;
			    SqlCmd.Parameters.Add("@TenDayDu", SqlDbType.NVarChar,50).Value=entity.TenDayDu;
			    SqlCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value=entity.NgaySinh;
			    SqlCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar,4000).Value=entity.DiaChi;
			    SqlCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar,20).Value=entity.SoDienThoai;
			    SqlCmd.Parameters.Add("@NgayThamGia", SqlDbType.DateTime).Value=entity.NgayThamGia;
			    SqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar,50).Value=entity.Email;
			    SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value=entity.TrangThai;

			    SqlAdp.UpdateCommand = SqlCmd;
			    SqlAdp.UpdateCommand.ExecuteNonQuery();
			    SqlCmd.Transaction.Commit();
			    boolUpdate = true ;
			    SqlCnn.Close();
		    }
		    catch
		    {
			    boolUpdate = false ;
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return boolUpdate;
	    }

        /// <summary>
		/// Deletes a record from the ThanhVien table by a composite primary key.
        /// <summary>
	    public bool Delete(Entity.ThanhVien entity)
	    {
		    bool boolDelete = false;
		    SqlConnection SqlCnn = db.ReadFromWebConfig();
		    SqlDataAdapter SqlAdp = new SqlDataAdapter();
		    SqlCommand SqlCmd = new SqlCommand();
		    SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_ThanhVien_Delete";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = entity.TenDangNhap;

			    SqlAdp.DeleteCommand = SqlCmd;
			    SqlAdp.DeleteCommand.ExecuteNonQuery();
			    SqlCmd.Transaction.Commit();
			    boolDelete = true ;
			    SqlCnn.Close();
		    }
		    catch
		    {
			    boolDelete = false ;
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return boolDelete;
	    }

        /// <summary>
        /// Members check email exists or not in the table
        /// <summary>
        public DataTable ExistsEmail(Entity.ThanhVien entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_ThanhVien_ExistsEmail";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = entity.Email;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }

        /// <summary>
        /// Members check TenDangNhap exists or not in the table
        /// <summary>
        public DataTable ExistsTenDangNhap(Entity.ThanhVien entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_ThanhVien_ExistsTenDangNhap";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = entity.TenDangNhap;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }

        /// <summary>
        /// Members check TenDangNhap exists or not in the table
        /// <summary>
        public DataTable LoginAdmin(Entity.ThanhVien entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_ThanhVien_LoginAdmin";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = entity.TenDangNhap;
                SqlCmd.Parameters.Add("@MatKhau", SqlDbType.NVarChar, 50).Value = entity.MatKhau;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
    }
}
